<?php

namespace App\Services;

use App\Models\RsReportAuth;
use App\Models\VReportOverdue;


use Illuminate\Contracts\Filesystem\FileNotFoundException;
use Illuminate\Support\Facades\Log;
use App\Models\VreportItReqProduct;
use App\Models\VReportVartimeDaysStage;
use App\Models\VReportTaskStageCompletion;
use App\Models\SOriginalFormatdocCategory;
use App\Models\SOriginalFormatdocCategoryOne;


use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;


class TableService extends BaseService
{
    /**
     * 展示报表数据
     * @return array
     */
    public function tables() : array
    {
        try{
            $data = RsReportAuth::with([
                'report' => function($query){
                    return $query->where('is_show_home',1);
                }
            ])
                ->where('user_id',auth('api')->user()->id)
                ->get()
                ->map(function ($item){
                    if (isset($item->report->id,$item->report->report_name) && !empty($item->report->id) && !empty($item->report->report_name)){
                        return [
                            'id'    => $item->report->id,
                            'name'  => $item->report->report_name
                        ];
                    }
                })
                ->toArray();

            return customer_return_success('success',$data);
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('报表查询 列表展示异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 获取到it服务单的分类
     * @return array
     */
    public function category() : array
    {
        try{
            $one = SOriginalFormatdocCategoryOne::select([
                'id','name'
            ])
                ->get()
                ->map(function ($item){
                    return [
                        'id'    => $item->id,
                        'value' => $item->name
                    ];
                })
                ->toArray();

            $two = SOriginalFormatdocCategory::select([
                'id','name'
            ])
                ->get()
                ->map(function ($item){
                    return [
                        'id'    => $item->id,
                        'value' => $item->name
                    ];
                })
                ->toArray();

            return customer_return_success('success',compact('one','two'));
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('报表查询 分类异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 获取到当前的状态
     * @return array
     */
    public function currentStatus() : array
    {
        try{
            $data =  VReportVartimeDaysStage::distinct('current_node')
                ->pluck('current_node')
                ->toArray();

            return customer_return_success('success',$data);
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('获取当前状态异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * IT 服务单报表
     * @param array $parameters
     * @return array
     */
    public function itServiceTable(array $parameters): array
    {
        try{
            $data = VreportItReqProduct::filter($parameters)
                ->orderBy('applytime','desc')
                ->paginate(10)
                ->toArray();

            $total = $data['total'];

            $data = $data['data'];

            return customer_return_success('success',compact('total','data'));
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('IT 服务单异常报表：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 逾期天数状态报表
     * @param array $parameters
     * @return array
     */
    public function overdueDaysStatusTable(array $parameters) : array
    {
        try{
            $data = VReportVartimeDaysStage::filter($parameters)
                ->orderBy('applyTime')
                ->paginate(10)
                ->toArray();

            $total = $data['total'];

            $data = $data['data'];

            return customer_return_success('success',compact('total','data'));
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('逾期天数状态报表异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 超时用户核对报表
     * @param array $parameters
     * @return array
     */
    public function overdueUserCheckTable(array $parameters): array
    {
        try{
            $data = VReportOverdue::filter($parameters)
                ->orderBy('plan_check_time')
                ->paginate(10)
                ->toArray();

            $total = $data['total'];

            $data = $data['data'];

            return customer_return_success('success',compact('total','data'));
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('超时用户核对报表异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 系统阶段任务完成报表
     * @param array $parameters
     * @return array
     */
    public function systemStageTaskCompletionTable(array $parameters) : array
    {
        try{
            $data = VReportTaskStageCompletion::filter($parameters)
                ->orderBy('applytime')
                ->paginate(10)
                ->toArray();

            $total = $data['total'];

            $data = $data['data'];

            return customer_return_success('success',compact('total','data'));
        }catch (\Exception $exception){
            Log::channel('api')
                ->info('系统阶段任务完成报表异常：'.$exception->getMessage());

            report($exception);

            return customer_return_error('数据异常，请稍后再试');
        }
    }

    /**
     * 根据type来展示数据
     * @param array $parameters
     * @return array
     * @throws FileNotFoundException
     */
    public function index(array $parameters): array
    {
        $parameters = $this->pageParameters($parameters);

        if ($parameters['type'] == 9) {
            if (!isset($parameters['year']) || empty($parameters['year'])) {
                $parameters['year'] = date("Y",time());
            }
        }

        $param = [];

        foreach ($parameters as $key => $value) {
            $value = addslashes($value);

            $value = str_replace("_", "\_", $value);
            $value = str_replace("%", "\%", $value);
            $value = nl2br($value);
            $value = htmlspecialchars($value);

            $value = str_replace("and","&#97;nd",$value);
            $value = str_replace("execute","&#101;xecute",$value);
            $value = str_replace("update","&#117;pdate",$value);
            $value = str_replace("count","&#99;ount",$value);
            $value = str_replace("chr","&#99;hr",$value);
            $value = str_replace("mid","&#109;id",$value);
            $value = str_replace("master","&#109;aster",$value);
            $value = str_replace("truncate","&#116;runcate",$value);
            $value = str_replace("char","&#99;har",$value);
            $value = str_replace("declare","&#100;eclare",$value);
            $value = str_replace("select","&#115;elect",$value);
            $value = str_replace("create","&#99;reate",$value);
            $value = str_replace("delete","&#100;elete",$value);
            $value = str_replace("insert","&#105;nsert",$value);
            $value = str_replace("'","&#39;",$value);
            $value = str_replace("+","",$value);
            $value = str_replace("%","",$value);
            $value = str_replace("&","",$value);
            $value = str_replace("<>","",$value);
            $value = str_replace(";","",$value);

            $param[$key] = $value;
        }

        $data = $this->analysisSql($parameters['type'],$param);

        return customer_return_success('success',$data);
    }

    /**
     * 添加分页数据
     * @param array $parameters
     * @return array
     */
    public function pageParameters(array $parameters): array
    {
        $page = $parameters['page'];

        $parameters['firstPage'] = ($page - 1)* 30;
//        $parameters['endPage']   = $page * 30;
        $parameters['endPage']   = 30;
        unset($parameters['page']);

        return $parameters;
    }

    /**
     * 根据type 获取到数据的原始数据源文件
     * @param int $type
     * @return string
     */
    public function getSqlFile(int $type) : string
    {
        switch ($type) {
            case 1:
                $sqlFile = 'it_server_withdrawal_record';
                break;
            case 2:
                $sqlFile = 'it_server_all_quantity_record';
                break;
            case 3:
                $sqlFile = 'start_end_overdue_prescription';
                break;
            case 4:
                $sqlFile = 'requirement_assessment_stage_overdue';
                break;
            case 5:
                $sqlFile = 'requirement_analysis_stage_overdue';
                break;
            case 6:
                $sqlFile = 'requirement_implementation_stage_overdue';
                break;
            case 7:
                $sqlFile = 'requirement_online_stage_overdue';
                break;
            case 8:
                $sqlFile = 'out_user_acceptance';
                break;
            case 9:
                $sqlFile = 'project_hours_report';
                break;
            default:
                $sqlFile = 'system_stage_complete_record';
        }

        $sqlFile = 'sql/' . $sqlFile . '.sql';

        return resource_path($sqlFile);
    }



    /**
     * 解析sql 返回数据
     * @param int $type
     * @param array $parameters
     * @return array
     * @throws FileNotFoundException
     */
    public function analysisSql(int $type,array $parameters) : array
    {
        $filePath = $this->getSqlFile($type);

        if (File::isFile($filePath) && File::isReadable($filePath)) {
            unset($parameters['type']);

            $sql = customer_analysis_string('${','}',File::get($filePath),$parameters);

            $sql = str_replace(PHP_EOL, '', $sql);

            $data = DB::connection('bpm')
                ->select($sql);

            if (!empty($data)) {
                $data = json_decode(json_encode($data),true);
            }
        }

        return $data ?? [];
    }
}
